﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Configuration;
using System.IO;
namespace CMSDataService
{
    partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }

        //Kavass zhu 2008/8/18 服务启动时响应的事件
        protected override void OnStart(string[] args)
       // public void OnStart()
        {
            // TODO: 在此处添加代码以启动服务。 
            //记录Service开始运行的时间
            Err_process("Service", "Service On " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " Start");
            timer1.Interval = 60000;
            timer1.Enabled = true;
        }

        //Kavass zhu 2008/8/18 服务结束时响应的事件
        protected override void OnStop()
        {
            // TODO: 在此处添加代码以执行停止服务所需的关闭操作。
            //记录Service结束运行的时间

            Err_process("Service", "Service On " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " End");

        }

        //Kavass zhu 2008/7/15 Editer,每天晚上12钟开始导数据
        private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            if ((DateTime.Now.Hour == 0) & (DateTime.Now.Minute == 01))
            {
                //记录开始导入数据的时间
                Err_process("Service", "Service On " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " Begin Import Data");
                insert_data_to_cms();
                //记录结束导入数据的时间
                Err_process("Service", "Service On " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " End Import Data");

            }
        }

        //Kavass zhu 2008/7/16 Editer,找出需要插入数据的Table和IEDS Select SQL语句
        private void insert_data_to_cms()
        {
            int CMSRowsCount;
            string iedsSql;
            string cmsConnString = ConfigurationManager.ConnectionStrings["CMSConn"].ToString();
            OracleConnection CMSConn = new OracleConnection(cmsConnString);
            DataTable cmsdt = new DataTable();
            OracleDataAdapter cmsAdapter = new OracleDataAdapter("", CMSConn);
            cmsAdapter.SelectCommand.CommandText = "select * from tbl_cms_data_service where delete_flag=0 order by NUM_ID";
            try
            {
                cmsAdapter.Fill(cmsdt);
                for (CMSRowsCount = 0; CMSRowsCount < cmsdt.Rows.Count; CMSRowsCount++)
                {
                    for (CMSRowsCount = 0; CMSRowsCount < cmsdt.Rows.Count; CMSRowsCount++)
                    {
                        if (cmsdt.Rows[CMSRowsCount]["insert_type"].ToString() == "0")
                        {
                            select_ieds(cmsdt.Rows[CMSRowsCount]["CMS_table_name"].ToString(), cmsdt.Rows[CMSRowsCount]["ieds_sql"].ToString());
                        }
                        else
                        {
                            cmsAdapter.DeleteCommand = new OracleCommand();
                            cmsAdapter.DeleteCommand.Connection = CMSConn;
                            string delsql = "delete from " + cmsdt.Rows[CMSRowsCount]["CMS_table_name"].ToString();
                            if (cmsAdapter.DeleteCommand.Connection.State != ConnectionState.Open)
                            {
                                cmsAdapter.DeleteCommand.Connection.Open();
                            }
                            cmsAdapter.DeleteCommand.CommandText = delsql;
                            cmsAdapter.DeleteCommand.ExecuteNonQuery();
                            select_ieds(cmsdt.Rows[CMSRowsCount]["CMS_table_name"].ToString(), cmsdt.Rows[CMSRowsCount]["ieds_sql"].ToString());
                        }

                    }
                }
                //导入IEDS库存
                cmsdt.Reset();
                cmsAdapter.SelectCommand.CommandText = "select * from tbl_ieds_stock where stock_date=to_date('" + DateTime.Now.ToString("yyyy/MM/dd") + "','yyyy/mm/dd')-1";
                cmsAdapter.Fill(cmsdt);
                if (cmsdt.Rows.Count<1)
                {
                    iedsSql = "exec UP_RepEmsImgStatus @EmsNo = 'E57204000002', @BeginDate = '2003/1/1 0:0:0', @EndDate ='" + DateTime.Now.Date.ToString("yyyy/MM/dd HH:mm:ss") + "',@GNos = '()', @GNoIsAll = 1, @CalFlag = 1, @OccurFlag = 1, @DateType = 0";
                    select_ieds("tbl_ieds_stock", iedsSql);
                }
            }
            catch (Exception err)
            {
                Err_process("CMS" + DateTime.Now.ToString("yyyyMMdd"), "--" + DateTime.Now.ToString() + "   " + err.Message + "\r\n" +err.Message);
            }
            finally
            {
                if (CMSConn != null && CMSConn.State != ConnectionState.Closed)
                {
                    CMSConn.Close();
                }
            }
        }

        //Kavass zhu 2008/7/16 Editer,组成插入数据的Insert语句
        private void select_ieds(string table_name, string iedsSql)
        {
            int IEDSRowsCount;
            int IEDSColumnsCount;

            string iedsConnString = ConfigurationManager.ConnectionStrings["IEDSConn"].ToString();
            SqlConnection IEDSConn = new SqlConnection(iedsConnString);
            SqlDataAdapter iedsAdapter = new SqlDataAdapter("", IEDSConn);


            iedsAdapter.SelectCommand.CommandText = iedsSql;
            iedsAdapter.SelectCommand.CommandTimeout = 600;
            DataTable iedsdt = new DataTable();

            try
            {
                iedsAdapter.Fill(iedsdt);
                for (IEDSRowsCount = 0; IEDSRowsCount < iedsdt.Rows.Count; IEDSRowsCount++)
                {
                    string cmsInsert = "insert into ";
                    string cmsValues = " values(";
                    //找出需要導入的數據的Table
                    cmsInsert += table_name + "(";

                    for (IEDSColumnsCount = 0; IEDSColumnsCount < iedsdt.Columns.Count; IEDSColumnsCount++)
                    {
                        if (IEDSColumnsCount == iedsdt.Columns.Count - 1)
                        {
                            cmsInsert += iedsdt.Columns[IEDSColumnsCount].ColumnName + ")";

                            if (iedsdt.Columns[IEDSColumnsCount].DataType == typeof(DateTime))
                            {
                                if (iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount].ToString() == "")
                                {
                                    cmsValues += "to_date('" + iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount].ToString() + "','yyyy/mm/dd HH24:mi:ss'))";
                                }
                                else
                                {
                                    cmsValues += "to_date('" + Convert.ToDateTime(iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount]).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy/mm/dd HH24:mi:ss'))";
                                }
                            }
                            else
                            {
                                cmsValues += "'" + iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount].ToString().Replace("'", "''") + "')";
                            }
                        }
                        else
                        {
                            cmsInsert += iedsdt.Columns[IEDSColumnsCount].ColumnName + ",";

                            if (iedsdt.Columns[IEDSColumnsCount].DataType == typeof(DateTime))
                            {
                                if (iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount].ToString() == "")
                                {
                                    cmsValues += "to_date('" + iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount].ToString() + "','yyyy/mm/dd HH24:mi:ss'),";
                                }
                                else
                                {
                                    cmsValues += "to_date('" + Convert.ToDateTime(iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount]).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy/mm/dd HH24:mi:ss'),";
                                }
                            }
                            else
                            {
                                cmsValues += "'" + iedsdt.Rows[IEDSRowsCount][IEDSColumnsCount].ToString().Replace("'", "''") + "',";
                            }
                        }
                    }

                    IEDS_TO_CMS(cmsInsert + cmsValues);
                }
            }
            catch (Exception err)
            {
                Err_process("IEDS" + DateTime.Now.ToString("yyyyMMdd"), "--" + DateTime.Now.ToString() + "   " + err.Message + "\r\n" + iedsSql);
            }
            finally
            {
                if (IEDSConn != null && IEDSConn.State != ConnectionState.Closed)
                {
                    IEDSConn.Close();
                }
            }
        }

        //Kavass zhu 2008/7/17 Editer,将IEDS中的数据插入到CMS中
        private void IEDS_TO_CMS(string insert_sql)
        {
            string cmsConnString = ConfigurationManager.ConnectionStrings["CMSConn"].ToString();
            OracleConnection CMSConn = new OracleConnection(cmsConnString);

            OracleDataAdapter cmsAdapter = new OracleDataAdapter();
            cmsAdapter.InsertCommand = new OracleCommand();
            cmsAdapter.InsertCommand.Connection = CMSConn;
            try
            {
                cmsAdapter.InsertCommand.CommandText = insert_sql;
                if (cmsAdapter.InsertCommand.Connection.State != ConnectionState.Open)
                {
                    cmsAdapter.InsertCommand.Connection.Open();
                }
                cmsAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                Err_process(DateTime.Now.ToString("yyyyMMdd"), "--"+DateTime.Now.ToString()+"   "+err.Message + "\r\n" + insert_sql);
            }
            finally
            {
                if (CMSConn != null && CMSConn.State != ConnectionState.Closed)
                {
                    CMSConn.Close();
                }
            }
        }

        //Kavass zhu 2008/7/18 Editer.错误处理
        private void Err_process(string filname, string errinfo)
        {
            if (!Directory.Exists("d:\\CMSDataService"))
            {
                Directory.CreateDirectory("d:\\CMSDataService");
            }
            StreamWriter sw = File.AppendText("d:\\CMSDataService\\" + filname + ".txt");
            sw.WriteLine(errinfo + ";");
            sw.Close();
        }



    }

}

